﻿/*ESRPConnectionString*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
/*Modificari pana la data de 01.02.2014*/
/**/


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaTGC2014]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalantaTGC2014]
GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaTGC2014]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[balcon_GetBalantaTGC2014] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@filtJurnal varchar(1000),
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@FiltruAnalitic TipCont
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = '',''
	SELECT @ConturiTGC=@ConturiTGC + Cont + '','' FROM ConturiSpeciale WHERE Tipuri like''%{TGC}%'' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = '',''
	SELECT @ConturiSS=@ConturiSS + Cont + '','' FROM ConturiSpeciale WHERE Tipuri like''%{CSS}%'' AND [CodUnitate]=@CodUnitate



	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM (SELECT a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],sum(a.[DebitPrecedent]) DebitPrecedent ,sum(a.[CreditPrecedent]) CreditPrecedent,
					sum(b.DebitInitial) [DebitInitial] ,sum(b.creditInitial) [CreditInitial]
					FROM [Conturi] a JOIN [Conturi] b
					ON dbo.IsContInclus(a.cont,b.cont,a.Terminal)=1 and a.codunitate=b.codunitate
					where a.[CodUnitate]=@CodUnitate and a.categorie=3 and b.ContParinte=''N/A''
					group by a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue]
					UNION
					SELECT  a2.[Cont],a2.[CodUnitate],a2.[Denumire],a2.[Tip],a2.[Terminal],a2.[ContParinte],a2.[EsteAnalitic],a2.[Categorie],a2.[SortValue],a2.[DebitPrecedent],a2.[CreditPrecedent],
						a2.[DebitInitial] ,a2.[CreditInitial] FROM [Conturi] a2
					WHERE a2.codunitate=@CodUnitate and a2.categorie=0 
				 ) c  LEFT OUTER JOIN 			
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [RulajeDoc] D 
							INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate 
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>''N/A'' 
				AND Categorie in (@pCategorie,3) 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='''' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='''' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			Rulaje rj 
			JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) 
					AND rj.[CodUnitate]=@CodUnitate 
					AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (rjd.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
			) --exists	 
			OR (LEN(LTRIM(RTRIM(@filtJurnal)))=0 AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn('',''+ Cont+'','',@ConturiTGC)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	AND (LEN(@FiltruAnalitic)=0 OR (Cont LIKE ''%'' + @FiltruAnalitic))
	ORDER BY Cont 
					

END


' 
END
GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaExtinsa2014]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalanta2014]
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaExtinsa2014]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[balcon_GetBalanta2014] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@filtJurnal varchar(1000),
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@SurseFinantare varchar(500),
	@FiltruAnalitic TipCont	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = '',''
	SELECT @ConturiTGC=@ConturiTGC + Cont + '','' FROM ConturiSpeciale WHERE Tipuri like''%{TGC}%'' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = '',''
	SELECT @ConturiSS=@ConturiSS + Cont + '','' FROM ConturiSpeciale WHERE Tipuri like''%{CSS}%'' AND [CodUnitate]=@CodUnitate




	SELECT  CASE WHEN LEN(c.Cont)>=22 THEN SUBSTRING(c.Cont,1,22) +''XXXXXXXXXXXXXXXXXX'' + SUBSTRING(c.Cont,23,17) ELSE c.Cont END Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM [Conturi] c LEFT OUTER JOIN 
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [RulajeDoc] D 
							INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate 
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON  dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>''N/A'' 
				AND Categorie =@pCategorie 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='''' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='''' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			Rulaje rj 
			JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) 
					AND rj.[CodUnitate]=@CodUnitate 
					AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (rjd.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
			) --exists	 
			OR (LEN(LTRIM(RTRIM(@filtJurnal)))=0  AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn('',''+ Cont+'','',@ConturiSS)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	AND (LEN(@surseFinantare)=0 OR (dbo.GetSursaFinantare(Cont) IN (SELECT fart.value FROM dbo.SplitParameters(@SurseFinantare,'','') fart)))	
	AND (LEN(@FiltruAnalitic)=0 OR (Cont LIKE ''%'' + @FiltruAnalitic))
	ORDER BY Cont 
					

END

' 
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaArh2014]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalantaArh2014]
GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaArh2014]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[balcon_GetBalantaArh2014] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@filtJurnal varchar(1000),
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@pAn smallint,
	@FiltruAnalitic TipCont
AS
BEGIN
	SET NOCOUNT ON;	

	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = '',''
	SELECT @ConturiTGC=@ConturiTGC + Cont + '','' FROM ConturiSpeciale WHERE Tipuri like''%{TGC}%'' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = '',''
	SELECT @ConturiSS=@ConturiSS + Cont + '','' FROM ConturiSpeciale WHERE Tipuri like''%{CSS}%'' AND [CodUnitate]=@CodUnitate




	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM [arhConturi] c LEFT OUTER JOIN 
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [arhRulajeDoc] D 
							INNER JOIN [arhRulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate AND d.An = @pAn
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [arhRulajeDoc] D 
							INNER JOIN [arhRulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate AND d.An = @pAn
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>''N/A'' 
				AND c.[An]=@pAn
				AND Categorie =@pCategorie 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='''' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='''' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			arhRulaje rj 
			JOIN  arhRulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) 
					AND rj.[CodUnitate]=@CodUnitate AND rjd.An = @pAn
					AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (rjd.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
			) --exists	 
			OR (LEN(LTRIM(RTRIM(@filtJurnal)))=0 AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn('',''+ Cont+'','',@ConturiSS)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	AND (LEN(@FiltruAnalitic)=0 OR (Cont LIKE ''%'' + @FiltruAnalitic))
	ORDER BY Cont 
					

END
' 
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalanta2014]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalanta2014]
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalanta2014]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[balcon_GetBalanta2014] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@filtJurnal varchar(1000),
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@SurseFinantare varchar(500),
	@FiltruAnalitic TipCont	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = '',''
	SELECT @ConturiTGC=@ConturiTGC + Cont + '','' FROM ConturiSpeciale WHERE Tipuri like''%{TGC}%'' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = '',''
	SELECT @ConturiSS=@ConturiSS + Cont + '','' FROM ConturiSpeciale WHERE Tipuri like''%{CSS}%'' AND [CodUnitate]=@CodUnitate




	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM [Conturi] c LEFT OUTER JOIN 
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [RulajeDoc] D 
							INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate 
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON  dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>''N/A'' 
				AND Categorie =@pCategorie 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='''' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='''' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			Rulaje rj 
			JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) 
					AND rj.[CodUnitate]=@CodUnitate 
					AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (rjd.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,'','') fart)))
			) --exists	 
			OR (LEN(LTRIM(RTRIM(@filtJurnal)))=0  AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn('',''+ Cont+'','',@ConturiSS)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	AND (LEN(@surseFinantare)=0 OR (dbo.GetSursaFinantare(Cont) IN (SELECT fart.value FROM dbo.SplitParameters(@SurseFinantare,'','') fart)))	
	AND (LEN(@FiltruAnalitic)=0 OR (Cont LIKE ''%'' + @FiltruAnalitic))
	ORDER BY Cont 
					

END

' 
END
GO







IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_GetDocumente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_GetDocumente]
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_GetDocumente]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[amix_GetDocumente]
	@CodUnitate smallint, 
	@MijloaceFixe bit,
	@DeLaData datetime,
	@PanaLaData datetime,
	@ContImobilizare varchar(2000),
	@ContAmortizare varchar(2000),
	@LocFunctionare varchar(4000),
	@Gestionar varchar(2000),
	@DenumireDocument varchar(2000),
	@SurseFinantare varchar(2000),
	@CategorieMF varchar(2000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM [AmixViewDocumente] WHERE [CodUnitate]=@CodUnitate 
	AND (@MijloaceFixe = 0 OR [Compus]<3)
	AND (@MijloaceFixe = 1 OR [Compus]>=3)
	AND [Anul]*100 + [Luna]>= YEAR(@DeLaData)*100 + MONTH(@DeLaData)
	AND [Anul]*100 + [Luna]<= YEAR(@PanaLaData)*100 + MONTH(@PanaLaData)
	AND (LEN(LTRIM(RTRIM(@ContImobilizare)))=0 OR (ContImobilizare IN (SELECT fart.value FROM dbo.SplitParameters(@ContImobilizare,'','') fart)))
	AND (LEN(LTRIM(RTRIM(@ContAmortizare)))=0 OR (ContAmortizare IN (SELECT fart.value FROM dbo.SplitParameters(@ContAmortizare,'','') fart)))
	AND (LEN(LTRIM(RTRIM(@LocFunctionare)))=0 OR (LocFunctionare IN (SELECT fart.value FROM dbo.SplitParameters(@LocFunctionare,'','') fart)))
	AND (LEN(LTRIM(RTRIM(@Gestionar)))=0 OR (Gestionar IN (SELECT fart.value FROM dbo.SplitParameters(@Gestionar,'','') fart)))
	AND (LEN(LTRIM(RTRIM(@DenumireDocument)))=0 OR (DenumireDocument IN (SELECT fart.value FROM dbo.SplitParameters(@DenumireDocument,'','') fart)))
	AND (LEN(LTRIM(RTRIM(@SurseFinantare)))=0 OR (IDSursaFinantare IN (SELECT fart.value FROM dbo.SplitParameters(@SurseFinantare,'','') fart)))
	AND (LEN(LTRIM(RTRIM(@CategorieMF)))=0 OR (CategorieMF IN (SELECT fart.value FROM dbo.SplitParameters(@CategorieMF,'','') fart)))
END

' 
END
GO



DROP PROCEDURE [dbo].[balcon_GenListaBalanta2014]
GO




CREATE  PROCEDURE [dbo].[balcon_GenListaBalanta2014]
	@An smallint   
	,@CodUnitate smallint
	,@categorie tinyint
	,@analitica bit
	,@cfm bit
	,@filtruJurnale varchar(500)
	,@DataStart datetime
	,@DataEnd datetime
	,@ptgc bit
	,@ContStart as TipCont
	,@ContEnd as TipCont
	,@Extinsa bit
	,@filtruSurseFinantare varchar(500)
	,@FiltruAnaliticPar TipCont
	,@useDataStart bit
AS

DECLARE @lDataStart as datetime
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @strcategorie as varchar(10)
DECLARE @dataStartFunctie varchar(50)
DECLARE @laData varchar(50)
DECLARE @ultimaData datetime


IF @useDataStart = 1
	SET @lDataStart = @DataStart
ELSE
	SET @lDataStart = DATEADD(Day, 1-DAY(@DataEnd),@DataEnd)



DECLARE	@return_value int
PRINT('DECLARE @ContStart as TipCont,@ContEnd as TipCont
DECLARE @DataStart as datetime, @DataEnd as datetime
DECLARE @categorie as tinyint
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @jurnal varchar(1000)
DECLARE @pAn smallint
DECLARE @ptgc bit
DECLARE @CodUnitate smallint
DECLARE	@return_value int
')

PRINT('SET @DataStart = ''' + cast(@DataStart as varchar(20)) + '''')
PRINT('SET @DataEnd=''' + cast(@DataEnd as varchar(100)) + '''')
PRINT('SET @jurnal=''' + @filtruJurnale +'''')
PRINT('SET @categorie=' + cast(@categorie as varchar(100)))
PRINT('SET @ContStart=''' + cast(@ContStart as varchar(100)) +'''')
PRINT('SET @ContEnd =''' + cast(@ContEnd as varchar(100)) + '''')
PRINT('SET @cfm=' + cast(@cfm as varchar(100)))
PRINT('SET @analitica=' + cast(@analitica as varchar(100)))
PRINT('SET @pAn=' + cast(@An as varchar(100)))
PRINT('SET @ptgc=' + cast(@ptgc as varchar(100)))
PRINT('SET @CodUnitate=' + cast(@CodUnitate as varchar(100)))

IF @An=0
BEGIN
	IF @ptgc = 1
		EXEC	@return_value = [dbo].[balcon_GetBalantaTGC2014]
		@CodUnitate = @CodUnitate,
		@pDtDataStartUI = @lDataStart,
		@pDtDataEndUI = @DataEnd,
		@filtJurnal = @filtruJurnale,
		@pCategorie = @categorie,
		@pStrContStart = @ContStart,
		@pStrContEnd = @ContEnd,
		@pCfm = @cfm,
		@pAnalitica = @analitica,
		@pCfmRulaje = 1,
		@pTerminal = 0,
		@FiltruAnalitic = @FiltruAnaliticPar
	ELSE
		IF @Extinsa=1
			EXEC	@return_value = [dbo].[balcon_GetBalantaExtinsa2014]
			@CodUnitate = @CodUnitate,
			@pDtDataStartUI = @lDataStart,
			@pDtDataEndUI = @DataEnd,
			@filtJurnal = @filtruJurnale,
			@pCategorie = @categorie,
			@pStrContStart = @ContStart,
			@pStrContEnd = @ContEnd,
			@pCfm = @cfm,
			@pAnalitica = @analitica,
			@pCfmRulaje = 1,
			@pTerminal = 0,
			@SurseFinantare = @filtruSurseFinantare,
			@FiltruAnalitic = @FiltruAnaliticPar
		ELSE
			EXEC	@return_value = [dbo].[balcon_GetBalanta2014]
			@CodUnitate = @CodUnitate,
			@pDtDataStartUI = @lDataStart,
			@pDtDataEndUI = @DataEnd,
			@filtJurnal = @filtruJurnale,
			@pCategorie = @categorie,
			@pStrContStart = @ContStart,
			@pStrContEnd = @ContEnd,
			@pCfm = @cfm,
			@pAnalitica = @analitica,
			@pCfmRulaje = 1,
			@pTerminal = 0,
			@SurseFinantare = @filtruSurseFinantare,
			@FiltruAnalitic = @FiltruAnaliticPar
END
ELSE
		EXEC	@return_value = [dbo].[balcon_GetBalantaArh2014]
		@CodUnitate = @CodUnitate,
		@pDtDataStartUI = @lDataStart,
		@pDtDataEndUI = @DataEnd,
		@filtJurnal = @filtruJurnale,
		@pCategorie = @categorie,
		@pStrContStart = @ContStart,
		@pStrContEnd = @ContEnd,
		@pCfm = @cfm,
		@pAnalitica = @analitica,
		@pCfmRulaje = 1,
		@pTerminal = 0,
		@pAn = @An,
		@FiltruAnalitic = @FiltruAnaliticPar


GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balantei',16,-1)
Gata:

GO


DROP PROCEDURE [dbo].[balcon_arhGenListaExecutieChelt]
GO



CREATE PROCEDURE [dbo].[balcon_arhGenListaExecutieChelt]   
@idLista smallint, 
@deLaData datetime,
@panaLaData datetime,
@contStart  dbo.TipCont,
@contEnd  dbo.TipCont,
@CodUnitate smallint, 
@strFiltruNrNota varchar(2000),
@centralizat bit,
@cfm bit,
@filtruSS varchar(500),
@filtruJurnal varchar(2000),
@An varchar(4)
AS



DECLARE @ca varchar(1000)
DECLARE @ab varchar(1000)
DECLARE @al varchar(1000)
DECLARE @det varchar(1000)
DECLARE @plati varchar(1000)
DECLARE @chelt varchar(2000)
DECLARE @toate varchar(6000)
DECLARE @nAn int


SET @det =','
SET @plati =','
SET @chelt =','
SET @ca =','
SET @al =','
SET @ab =','
SET @nAn = CAST(@An as int)

SELECT @det=@det + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{CD}%' AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @ca=@ca + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{CA}%'  AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @ab=@ab + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{AB}%'  AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @al=@al + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{AL}%' AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @plati=@plati + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '7%'  AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @chelt=@chelt +  Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '6%'  AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SET @toate = @det+@chelt+@plati+@ca+@ab+@al

PRINT @det
PRINT @ca
PRINT @ab
PRINT @al
Print @plati
Print @chelt
PRINT @toate

--DROP TABLE #TempExecChelt


	SELECT a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire+a.Denumire+a.TermDenumire Denumire,a.Anexa6,a.Terminal,a.Titlu,
		  SUM(COALESCE(Rul.CrediteAprobate,0)) CrediteAprobate, 
		  SUM(COALESCE(Rul.CreditDeschis,0)) CrediteDeschise, 
		  SUM(COALESCE(Rul.AngajamenteBugetare,0)) AngajamenteBugetare, 
		  SUM(COALESCE(Rul.AngajamenteLegale+Cnt.[DebitInitial]-Cnt.[CreditInitial],0)) AngajamenteLegale, 
		  SUM(COALESCE(Rul.PlatiNete,0)) PlatiNete,
		  SUM(COALESCE(Rul.[CreditDeschis],0)) - SUM(COALESCE(Rul.PlatiNete,0)) DisponibilCredit,
		  SUM(COALESCE(Rul.Cheltuieli,0)) Cheltuieli, 
		  SUM(COALESCE(Cnt.[DebitInitial],0)) DebitInitial,
		  SUM(COALESCE(Cnt.[CreditInitial],0)) CreditInitial
	INTO #TempExecChelt
	FROM (SELECT * FROM [arhArticoleAliniate] WITH(NOLOCK) WHERE CodUnitate=@CodUnitate AND An=@nAn) a 
	LEFT OUTER JOIN
	(SELECT --pentru soldurile initiale la angajamente legale
		  dbo.GetArticolAliniat(Cont) ArtAlCont,[CodUnitate],[An],
		  sum(DebitInitial) DebitInitial,
		  sum(CreditInitial) CreditInitial
		  FROM [arhConturi] WITH(NOLOCK)
		  WHERE [CodUnitate] = @CodUnitate AND dbo.IsContIn(','+ dbo.GetSintetic(Cont)+',',@al)<>0 
				AND (LEN(@contStart)=0 OR Cont>=@contStart)
				AND (LEN(@contEnd)=0 OR Cont<=@contEnd)
				AND Terminal = 1
				AND An=@nAn
		  GROUP BY dbo.GetArticolAliniat(Cont),[CodUnitate],[An]
	  ) CNT
	ON a.ArtAl=CNT.ArtAlCont AND a.[CodUnitate]=CNT.[CodUnitate] AND a.[An]=CNT.[An]
	LEFT OUTER JOIN 
	(SELECT  
					  dbo.GetArticolAliniat(COALESCE(t2.ContCredit, t1.ContDebit)) ArtAlCont, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@ca,SumaDebit)) CrediteAprobate, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@det,SumaDebit)) CreditDeschis, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@ab,SumaDebit)) AngajamenteBugetare, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@al,SumaDebit)) AngajamenteLegale, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@plati,COALESCE(SumaCredit,0)-COALESCE(SumaDebit,0))) PlatiNete, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@chelt,SumaDebit)) Cheltuieli 
				FROM 
					  (SELECT SUM(coalesce(F.ValoareLei,0)) sumaDebit, F.ContDebit 
					  FROM [arhRulajeDoc] D  INNER JOIN [arhRulaje] F ON D.[IDDocument] = F.[IDDocument] AND D.[CodUnitate] = @CodUnitate AND D.[an]=F.[An]  
					  WHERE 
					  F.ContDebit<>'N/A'  AND  dbo.IsContIn(','+ dbo.GetSintetic(F.ContDebit)+',',@toate)<>0  
					  AND D.DataDoc>=@deLaData AND D.DataDoc<=@panaLaData
					  AND (LEN(@contStart)=0  OR ContDebit>=@contStart)
					  AND (LEN(@contEnd)=0  OR ContDebit<=@contEnd)
					  AND D.[An]=@nAn
					  AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))             
					  AND (LEN(@filtruSS)=0 OR (dbo.GetSursaFinantare(F.ContDebit) IN (SELECT fart.value FROM dbo.SplitParameters(@filtruSS,',') fart)))
					  AND (LEN(LTRIM(RTRIM(@filtruJurnal)))=0 OR (IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@filtruJurnal,',') fart)))
					GROUP BY F.ContDebit) t1 

				  FULL OUTER JOIN 
					  (SELECT SUM(coalesce(F.ValoareLei,0)) sumaCredit, F.ContCredit
					  FROM [arhRulajeDoc] D WITH(NOLOCK)  INNER JOIN [arhRulaje] F  WITH(NOLOCK) ON D.[IDDocument] = F.[IDDocument] AND D.[CodUnitate] = @CodUnitate AND D.[An]=F.[An]
					  WHERE 
					  F.ContCredit <>'N/A' AND  dbo.IsContIn(','+ dbo.GetSintetic(F.ContCredit)+',',@toate)<>0  
					  AND D.DataDoc>=@deLaData AND D.DataDoc<=@panaLaData 
					  AND (LEN(@contStart)=0  OR ContCredit>=@contStart)
					  AND (LEN(@contEnd)=0  OR ContCredit<=@contEnd)
					  AND D.[An]=@nAn
					  AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))
					  AND (LEN(@filtruSS)=0 OR (dbo.GetSursaFinantare(F.ContCredit) IN (SELECT fart.value FROM dbo.SplitParameters(@filtruSS,',') fart)))
					  AND (LEN(LTRIM(RTRIM(@filtruJurnal)))=0 OR (IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@filtruJurnal,',') fart)))
					  GROUP BY F.ContCredit) t2 
				ON t1.ContDebit = t2.ContCredit 
	WHERE dbo.GetArticolAliniat(COALESCE(t2.ContCredit, t1.ContDebit))<>'XXXXXX'            
	GROUP BY dbo.GetArticolAliniat(COALESCE(t2.ContCredit, t1.ContDebit))) RUL
	ON  rul.ArtAlCont = LTRIM(RTRIM(a.ArtAl))
	WHERE   a.ArtAl <>'N/A'  
	GROUP BY  a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire,a.Denumire,a.TermDenumire,a.Anexa6,a.Terminal,a.Titlu


UPDATE #TempExecChelt SET [CrediteAprobate] = d.ca,
[CrediteDeschise] = d.cd,
[AngajamenteBugetare] = d.ab,
[AngajamenteLegale] = d.al,
[PlatiNete] = d.pn,
[DisponibilCredit] = d.dc,
[Cheltuieli] = d.c
FROM
(SELECT AAParinte,SUM([CrediteAprobate]) ca,SUM([CrediteDeschise]) cd,SUM([AngajamenteBugetare]) ab, 
SUM([AngajamenteLegale]) al,SUM([PlatiNete]) pn,SUM([DisponibilCredit]) dc,SUM([Cheltuieli]) c
FROM #TempExecChelt a GROUP BY AAParinte) d 
WHERE d.AAParinte=#TempExecChelt.ArtAl


UPDATE #TempExecChelt SET [CrediteAprobate] = d.ca,
[CrediteDeschise] = d.cd,
[AngajamenteBugetare] = d.ab,
[AngajamenteLegale] = d.al,
[PlatiNete] = d.pn,
[DisponibilCredit] = d.dc,
[Cheltuieli] = d.c
FROM
(SELECT AAParinte,SUM([CrediteAprobate]) ca,SUM([CrediteDeschise]) cd,SUM([AngajamenteBugetare]) ab, 
SUM([AngajamenteLegale]) al,SUM([PlatiNete]) pn,SUM([DisponibilCredit]) dc,SUM([Cheltuieli]) c
FROM #TempExecChelt a GROUP BY AAParinte) d 
WHERE d.AAParinte=#TempExecChelt.ArtAl
--AND [Categorii]='[TOT]'



UPDATE #TempExecChelt SET [CrediteAprobate] = d.ca,
[CrediteDeschise] = d.cd,
[AngajamenteBugetare] = d.ab,
[AngajamenteLegale] = d.al,
[PlatiNete] = d.pn,
[DisponibilCredit] = d.dc,
[Cheltuieli] = d.c
FROM
(SELECT AAParinte,SUM([CrediteAprobate]) ca,SUM([CrediteDeschise]) cd,SUM([AngajamenteBugetare]) ab, 
SUM([AngajamenteLegale]) al,SUM([PlatiNete]) pn,SUM([DisponibilCredit]) dc,SUM([Cheltuieli]) c
FROM #TempExecChelt a GROUP BY AAParinte) d 
WHERE d.AAParinte=#TempExecChelt.ArtAl
--AND [Categorii]='[TOT]'


SELECT * FROM #TempExecChelt
where ((@centralizat=0) OR ([Categorii]='[TOT]' OR Anexa6=1))  
AND (@cfm=1 OR ([CrediteAprobate] <>0 OR [AngajamenteBugetare]<>0 OR [AngajamenteLegale]<>0 OR [CrediteDeschise] <> 0 OR [PlatiNete] <> 0 OR [Cheltuieli] <> 0))
ORDER BY ArtAl


GOTO Gata
EROARE:
      RAISERROR ('Eroare la generarea balantei',16,-1)
      GOTO Gata
EROARE1:
      RAISERROR ('Va rugam completati capitolul pentru lista curenta!!',16,-1)      
      GOTO Gata
Gata:




GO

















